package com.navi.loaddata.loadService.impl.mesData.glassData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.File.FileGlassPojo;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Service
public class LoadMESGlassInfoService {
    private static final Logger logger = LoggerFactory.getLogger(LoadMESGlassInfoService.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());

    @Autowired
    private MySqlRepositoryImpl dao;

    public List<FileGlassPojo> getMESGlassInfo(EtlConfDModel etlConfD) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String startTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());

        List<FileGlassPojo> glassList = new ArrayList<>();

        QueryRunner qr_mes_user = JdbcUtil.getQueryRunner_mes_user();
        try {
            String sql = "SELECT TO_CHAR(A.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS') EVTTIMESTAMP, A.PRODUCTNAME GLASSID, A.DEFECTCODES GLASSREJCODE, A.OLDPRODUCTSPECNAME PRODID, A.OLDPROCESSOPERATIONNAME OPEID, A.PRODUCTGRADE GLASSGRADE, A.SUBFACTORYNAME SHOPID, '3' AS SHOPKEYFK, A.MACHINENAME EQPTID, A.CFPRODUCTNAME CFGLASSID, A.OWNERID OWNER, A.EVENTUSER EVTUSER, A.REWORKCOUNT AS JGECNT, A.QTAPID AS LCDLOT, TO_CHAR(A.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS') AS PROCENDTIME, 0 AS GLASSDEFECTCNT" +
                    " FROM P1MESADM.PRODUCTHISTORY A" +
                    " WHERE A.TIMEKEY >= '" + startTime + "'" +
                    " AND A.TIMEKEY < '" + endTime + "'" +
                    " AND A.OLDPROCESSOPERATIONNAME = '"+ etlConfD.getOpe() + "'" +
                    " AND A.EVENTNAME = 'TrackOut'";


            logger.info("查询Glass信息的SQL：" + sql);
            glassList = qr_mes_user.query(sql, new BeanListHandler<FileGlassPojo>(FileGlassPojo.class));
            logger.info("查询Glass信息数据行：" + glassList.size());
        } catch (Exception e) {
            logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
            throw new ETLException2(ErrorCode.ERROR_GET_MES_GLASS_INFO, e);
        }
        return glassList;
    }

    public List<FileGlassPojo> getMESGlassDefectCnt(EtlConfDModel etlConfD) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String startTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());

        List<FileGlassPojo> defectList = new ArrayList<>();

        QueryRunner qr_mes_user = JdbcUtil.getQueryRunner_mes_user();
        try {
            String sql = "SELECT TO_CHAR(EVENTTIME, 'YYYY-MM-DD HH24:MI:SS') AS EVTTIMESTAMP, PROCESSOPERATIONNAME AS OPEID, PRODUCTNAME AS GLASSID, COUNT(1) AS GLASSDEFECTCNT" +
                    " FROM P1MESADM.CT_PRODUCTDEFECTINFO" +
                    " WHERE TIMEKEY >= '" + startTime + "'" +
                    " AND TIMEKEY < '" + endTime + "'" +
                    " AND PROCESSOPERATIONNAME = '"+ etlConfD.getOpe() + "'" +
                    " AND EVENTNAME = 'TrackOut'" +
                    " GROUP BY TO_CHAR(EVENTTIME, 'YYYY-MM-DD HH24:MI:SS'), PROCESSOPERATIONNAME, PRODUCTNAME";


            logger.info("查询Glass的Defect数量的SQL：" + sql);
            defectList = qr_mes_user.query(sql, new BeanListHandler<FileGlassPojo>(FileGlassPojo.class));
            logger.info("查询Glass的Defect数量：" + defectList.size());
        } catch (Exception e) {
            logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
            throw new ETLException2(ErrorCode.ERROR_GET_MES_DEFECT_INFO, e);
        }
        return defectList;
    }


    @Transactional(transactionManager = "primaryPlatformTransactionManager")
    public void updateEtlConfD(EtlConfDModel etlConfD) {
        dao.update(etlConfD);
    }
}
